﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Globalization
Public Class MyObj
    Public Shared BaseYear As Integer = 2010

    Public Shared Function GetCodeMappingData(ByVal tableName As String, ByVal columnName As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select cm.* ")
        sql.AppendFormat("from CodeMapping cm ")
        sql.AppendFormat("where cm.TableName = '{0}' ", tableName)
        sql.AppendFormat("and cm.ColumnName = '{0}' ", columnName)
        sql.AppendFormat("and Active='Y' ")
        sql.AppendFormat("order by cm.Sequence ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    Public Shared Function GetCodeMappingData(ByVal tableName As String, ByVal columnName As String, ByVal columnExpression As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select cm.*, {0} ", columnExpression)
        sql.AppendFormat("from CodeMapping cm ")
        sql.AppendFormat("where cm.TableName = '{0}' ", tableName)
        sql.AppendFormat("and cm.ColumnName = '{0}' ", columnName)
        sql.AppendFormat("order by cm.Sequence ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    Public Shared Sub ShowMessage(ByVal message As String)
        If message IsNot Nothing Then
            Dim NewMessage As String = message.ToString()
            Dim js As String

            js = String.Format("alert(""{0}"");", NewMessage)
            ScriptManager.RegisterClientScriptBlock(TryCast(HttpContext.Current.Handler, Page), GetType(String), NewMessage, js, True)
        End If
    End Sub

    Public Shared Sub ShowMessage(ByVal format As String, ByVal ParamArray args() As Object)
        MyObj.ShowMessage(String.Format(format, args))
    End Sub

    Public Shared Sub WriteLog(ByVal message As String)
        Dim fileName As String = HttpContext.Current.Server.MapPath(String.Format("~\log\{0:yyyyMMdd}.txt", DateTime.Now))
        Dim sr As New IO.StreamWriter(fileName, True)

        sr.WriteLine(message)

        sr.Close()
    End Sub
    Public Shared Sub IsVisibleControl(ByVal UseButton As Control, ByVal Parent As Integer, ByVal ProgramID As String)
        If UseButton IsNot Nothing Then
            Dim sql As New MyStringBuilder
            Dim dat As New clsData
            Dim dt As Data.DataTable

            sql.Length = 0
            sql.AppendFormat("select distinct isnull(b.ReadOnly,0) as ReadOnly ")
            sql.AppendFormat("from fn_GetAllRoleIDByUserID('{0}') a ", clsAuth.GetUserID())
            sql.AppendFormat("join RoleProgramMapping b on a.Value=b.RoleID ")
            sql.AppendFormat("WHERE 1=1 ")
            sql.AppendFormat("and b.ProgramID='{0}' ", ProgramID)
            sql.AppendFormat("and isnull(b.ReadOnly,0)='{0}' ", 1)
            'MyObj.WriteLog(sql.ToString())

            dt = dat.GetData(sql)

            If dt.Rows.Count > 0 Then
                UseButton.Visible = False
            Else
                UseButton.Visible = True
            End If
        End If
    End Sub

    '取出擁有該頁面的權限(FullAccess、LimitAccess、ReadOnly)
    Public Shared Function GetPageAuth(ByVal ProgramID As String, ByVal UserID As String) As String
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select distinct isnull(b.ReadOnly,0) as ReadOnly ")
        sql.AppendFormat(",isnull(b.LimitAccess,0) as LimitAccess ")
        sql.AppendFormat("from fn_GetAllRoleIDByUserID('{0}') a ", UserID)
        sql.AppendFormat("join RoleProgramMapping b on a.Value=b.RoleID ")
        sql.AppendFormat("WHERE 1=1 ")
        sql.AppendFormat("and b.ProgramID='{0}' ", ProgramID)

        dt = dat.GetData(sql)

        If dt.Rows.Count > 0 Then
            Dim TempAuth As String = ""
            For i As Integer = 0 To dt.Rows.Count - 1
                If dt.Rows(0)("ReadOnly").ToString() = "0" AndAlso dt.Rows(0)("LimitAccess").ToString() = "0" Then
                    TempAuth = "FullAccess"
                    Exit For
                ElseIf dt.Rows(0)("LimitAccess").ToString() = "1" Then
                    TempAuth = "LimitAccess"
                ElseIf dt.Rows(0)("ReadOnly").ToString() = "1" Then
                    If TempAuth = "" Then
                        TempAuth = "ReadOnly"
                    End If
                End If
            Next
            Return TempAuth
        Else
            Return ""
        End If
    End Function

    '取出LimitMonth
    Public Shared Function GetLimitMonth(ByVal sig As String) As Integer
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select isnull(LimitMonth,0) as LimitMonth ")
        sql.AppendFormat("from TCReportFixedRule ")
        sql.AppendFormat("WHERE 1=1 ")
        sql.AppendFormat("and Signature='{0}' ", sig)

        dt = dat.GetData(sql)

        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("LimitMonth").ToString()
        Else
            Return 0
        End If
    End Function

    '取出該版本可以填寫的YYMM
    Public Shared Function GetLimitYYMM(ByVal MonthCode As String, ByVal sig As String, ByVal ProgramID As String, Optional ByVal GetAuth As String = "") As List(Of String)
        Dim MyList As New List(Of String)
        Dim MonStart As Integer
        Dim MonEnd As Integer
        Dim MyAuth As String
        If GetAuth <> "" Then
            MyAuth = GetAuth
        Else
            MyAuth = MyObj.GetPageAuth(ProgramID, clsAuth.GetUserID())
        End If

        If Right(MonthCode, 1) <> "B" And Right(MonthCode, 1) <> "T" Then
            MonStart = Right(MonthCode, 2)
        End If

        If MyAuth = "FullAccess" Then
            MonEnd = 36
        ElseIf MyAuth = "ReadOnly" Then
            MonEnd = 0
        Else
            Dim LimitMonth As Integer = MyObj.GetLimitMonth(sig)

            Dim sql As New MyStringBuilder
            Dim dat As New clsData

            sql.Length = 0
            sql.AppendFormat("Select Right(isnull(MaxID,1),2) from TCReportRefer ")
            sql.AppendFormat("where Division='{0}' and TableName='TCReport{1}' ", sig, MonthCode)

            If Right(MonthCode, 1) = "B" Then
                MonStart = 1
            ElseIf Right(MonthCode, 1) = "T" Then
                Dim dt As Data.DataTable

                sql.Length = 0
                sql.AppendFormat("Select Right(isnull(MaxID,1),2) from TCReportRefer ")
                sql.AppendFormat("where Division='{0}' and TableName='TCReport{1}' ", sig, MonthCode)
                sql.AppendFormat("and LEFT('{0}',4)=LEFT(MaxID,4) ", MonthCode)
                dt = dat.GetData(sql)

                If dt.Rows.Count = 0 Then
                    MonStart = 1
                Else
                    MonStart = dt.Rows(0)(0).ToString()
                End If
            End If

            MonEnd = MonStart + LimitMonth - 1
        End If

        If MonEnd <> 0 Then
            Dim MyYear As Integer = Left(MonthCode, 4)
            Dim AddYear, LeftMonth As Integer
            Dim NewYYMM As String = ""

            For i As Integer = MonStart To MonEnd
                AddYear = Math.DivRem(i, 12, LeftMonth)
                If LeftMonth = 0 Then
                    AddYear -= 1
                    LeftMonth = 12
                End If
                NewYYMM = (MyYear + AddYear) & Right("0" & LeftMonth, 2)
                MyList.Add(NewYYMM)
            Next
        End If
        Return MyList
    End Function

    ''' <summary>
    ''' 取得新增完畢之 row id
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Function GetIdentity() As Integer
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select @@identity ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0).Rows(0)(0)
    End Function
    Public Shared Sub SetDateLocale(ByVal region As String)
        Select Case region
            Case "zh-TW"
                Dim culture As New System.Globalization.CultureInfo("zh-TW")

                culture.DateTimeFormat.Calendar = New System.Globalization.TaiwanCalendar()
                culture.DateTimeFormat.ShortDatePattern = "yyyy.MM.dd"
                culture.DateTimeFormat.YearMonthPattern = "民國yy年MM月"
                culture.DateTimeFormat.ShortTimePattern = ""
                culture.DateTimeFormat.LongTimePattern = ""

                System.Threading.Thread.CurrentThread.CurrentCulture = culture
                System.Threading.Thread.CurrentThread.CurrentUICulture = culture
            Case ""
                Dim culture As New System.Globalization.CultureInfo("zh-TW", False)
                culture.DateTimeFormat.Calendar = New System.Globalization.GregorianCalendar()

                System.Threading.Thread.CurrentThread.CurrentCulture = culture
                System.Threading.Thread.CurrentThread.CurrentUICulture = culture
        End Select
    End Sub

    Public Shared Function GetDDLSelectAllItem() As ListItem
        Dim li As New ListItem

        li.Text = "All"
        li.Value = ""

        Return li
    End Function

    Public Shared Function GetDDLSelectAllItemV2() As ListItem
        Dim li As New ListItem

        li.Text = "All"
        li.Value = MyObj.SelectAllValue

        Return li
    End Function

    Public Shared Function GetDDLPleaseChooseItem() As ListItem
        Dim li As New ListItem

        li.Text = "- select -"
        li.Value = ""

        Return li
    End Function

    Public Shared Function GetDDLPleaseChooseItemV2() As ListItem
        Dim li As New ListItem

        li.Text = "- select -"
        li.Value = MyObj.PleaseChooseValue

        Return li
    End Function

    Public Shared Function GetParameter(ByVal parameterType As String, ByVal parameterName As String) As String
        Dim sql As New StringBuilder
        Dim dat As New clsData
        Dim dt As DataTable

        sql.AppendFormat("select ParameterValue ")
        sql.AppendFormat("from SystemParameter p ")
        sql.AppendFormat("where ParameterType = '{0}' ", parameterType)
        sql.AppendFormat("and ParameterName = '{0}' ", parameterName)
        sql.AppendFormat("and Active = '{0}' ", "Y")

        dt = dat.GetData(sql)
        If dt.Rows.Count <> 0 Then
            Return dt.Rows(0)(0).ToString()
        Else
            Return ""
        End If
    End Function

    Public Shared Sub MatchItem(ByVal Item As DropDownList, ByVal Value As String)
        If Item IsNot Nothing And Value IsNot Nothing Then
            Item.SelectedIndex = Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))
        End If
    End Sub

    Public Shared Sub MatchItem(ByVal Item As RadioButtonList, ByVal Value As String)
        If Item IsNot Nothing And Value IsNot Nothing Then
            Item.SelectedIndex = Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))
        End If
    End Sub
    Public Shared Sub MatchItem(ByVal Item As CheckBoxList, ByVal Value As String)
        If Item IsNot Nothing And Value IsNot Nothing Then
            'Item.SelectedIndex = Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))
            Item.Items(Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))).Selected = True
        End If
    End Sub
    ''' <summary>
    ''' 依登入者權限列出所有組織
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDDLOrganizationData() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select ua.*, o.OrganizationName, UOrganizationID ")
        sql.AppendFormat("from UserOrganizationAuth ua ")
        sql.AppendFormat("inner join OrganizationView o on ua.OrganizationID = o.OrganizationID and ua.VOrganizationID = o.VOrganizationID ")
        sql.AppendFormat("where ua.UserID = '{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and o.VOrganizationID = -1 ")
        sql.AppendFormat("order by o.VOrganizationCode, o.OrganizationCode ")

        dt = dat.GetData(sql)

        Return dat.GetData(sql)
    End Function

    '傳回對應欄位字母
    Public Shared Function ConvertToLetter(ByVal ColumnNumber As Integer) As String
        Dim ColumnLetter As String = ""
        If ColumnNumber <= 0 Then
            'negative column number
            ColumnLetter = ""

        ElseIf ColumnNumber > 16384 Then
            'column not supported (too big) in Excel 2007
            ColumnLetter = ""

        ElseIf ColumnNumber > 702 Then
            ' triple letter columns
            ColumnLetter = _
            Chr((Int((ColumnNumber - 1 - 26 - 676) / 676)) Mod 676 + 65) & _
            Chr((Int((ColumnNumber - 1 - 26) / 26) Mod 26) + 65) & _
            Chr(((ColumnNumber - 1) Mod 26) + 65)

        ElseIf ColumnNumber > 26 Then
            ' double letter columns
            ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                    Chr(((ColumnNumber - 1) Mod 26) + 65)
        Else
            ' single letter columns
            ColumnLetter = Chr(ColumnNumber + 64)
        End If

        Return ColumnLetter
    End Function

    '傳回英文月份
    Public Shared Function GetMonthText(ByVal Month As Integer) As String
        Dim MonthText As String = ""
        Select Case Month
            Case 1
                MonthText = "Jan"
            Case 2
                MonthText = "Feb"
            Case 3
                MonthText = "Mar"
            Case 4
                MonthText = "Apr"
            Case 5
                MonthText = "May"
            Case 6
                MonthText = "Jun"
            Case 7
                MonthText = "Jul"
            Case 8
                MonthText = "Aug"
            Case 9
                MonthText = "Sep"
            Case 10
                MonthText = "Oct"
            Case 11
                MonthText = "Nov"
            Case 12
                MonthText = "Dec"
        End Select

        Return MonthText
    End Function

    Public Shared Property xlsDataSFReport() As String
        Get
            If HttpContext.Current.Session("xlsDataSFReport") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataSFReport")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataSFReport") = value
        End Set
    End Property

    Public Shared Property xlsDataChannelSkuType() As String
        Get
            If HttpContext.Current.Session("xlsDataChannelSkuType") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataChannelSkuType")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataChannelSkuType") = value
        End Set
    End Property

    Public Shared Property xlsDataChannelSpectrum() As String
        Get
            If HttpContext.Current.Session("xlsDataChannelSpectrum") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataChannelSpectrum")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataChannelSpectrum") = value
        End Set
    End Property

    Public Shared Property xlsDataProductSpectrum() As String
        Get
            If HttpContext.Current.Session("xlsDataProductSpectrum") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataProductSpectrum")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataProductSpectrum") = value
        End Set
    End Property

    Public Shared Property xlsData() As String
        Get
            If HttpContext.Current.Session("xlsData") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsData")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsData") = value
        End Set
    End Property

    '下方資料表連結用的  Mod By Penny 20100831
    Public Shared Property xlsDataCompare() As String
        Get
            If HttpContext.Current.Session("xlsDataCompare") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataCompare")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataCompare") = value
        End Set
    End Property
    Public Shared Property xlsDataCompare2() As String
        Get
            If HttpContext.Current.Session("xlsDataCompare2") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataCompare2")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataCompare2") = value
        End Set
    End Property
    Public Shared Property xlsDataCompare3() As String
        Get
            If HttpContext.Current.Session("xlsDataCompare3") Is Nothing Then
                Return MyObj.EmptyXML
            Else
                Return HttpContext.Current.Session("xlsDataCompare3")
            End If
        End Get
        Set(ByVal value As String)
            HttpContext.Current.Session("xlsDataCompare3") = value
        End Set
    End Property

    Public Shared ReadOnly Property EmptyXML() As String
        Get
            If HttpContext.Current.Session("EmptyXML") Is Nothing Then
                Dim objReader As New IO.StreamReader(HttpContext.Current.Server.MapPath("~\App_Data\Empty.xml"))
                Dim fileContent As String = objReader.ReadToEnd()
                objReader.Close()
                objReader.Dispose()
                HttpContext.Current.Session("EmptyXML") = fileContent
            End If

            Return HttpContext.Current.Session("EmptyXML")
        End Get
    End Property

    Public Shared ReadOnly Property xlsUrlChannelSkuType() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderChannelSkuType.aspx")
        End Get
    End Property
    Public Shared ReadOnly Property xlsUrlChannelSpectrum() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderChannelSpectrum.aspx")
        End Get
    End Property

    Public Shared ReadOnly Property xlsUrlProductSpectrum() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderProductSpectrum.aspx")
        End Get
    End Property

    Public Shared ReadOnly Property xlsUrlSFReport() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderSFReport.aspx")
        End Get
    End Property

    Public Shared ReadOnly Property xlsUrl() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReader.aspx")
        End Get
    End Property

    '下方資料表連結用的
    Public Shared ReadOnly Property xlsUrlCompare() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderCompare.aspx")
        End Get
    End Property
    Public Shared ReadOnly Property xlsUrlCompare2() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderCompare2.aspx")
        End Get
    End Property
    Public Shared ReadOnly Property xlsUrlCompare3() As String
        Get
            Return VirtualPathUtility.ToAbsolute("~/WebForm/CommonControl/xlsReaderCompare3.aspx")
        End Get
    End Property

    Public Shared Function GetXmlUrl(ByVal SessionID As String) As String
        Return "~/WebForm/CommonControl/xlsReaderData.aspx?SessionID=" & SessionID
    End Function

    Public Shared Function JoinFormat(ByVal separator As String, ByVal format As String, ByVal valueList As IEnumerable) As String
        Dim tmp As New List(Of String)

        For Each v As Object In valueList
            tmp.Add(String.Format(format, v))
        Next

        Return String.Join(separator, tmp.ToArray)
    End Function

    Public Shared Function JoinFormat(ByVal separator As String, ByVal format As String, ByVal valueList As IList, ByVal valueList2 As IList) As String
        Dim tmp As New List(Of String)

        For i As Integer = 0 To valueList.Count - 1
            tmp.Add(String.Format(format, valueList(i), valueList2(i)))
        Next

        Return String.Join(separator, tmp.ToArray)
    End Function

    Public Shared Function GetReportYYMM(ByVal year As Integer, ByVal reportCode As String) As String
        Dim imonth As Integer

        If Integer.TryParse(reportCode, imonth) Then
            reportCode = imonth.ToString("00")
        End If

        Return String.Format("{0}{1}", year, reportCode)
    End Function

    Public Shared Function GetReportName(ByVal reportType As String, ByVal yymm As String) As String
        Dim reportPrefix As String = ""

        Select Case reportType
            Case "TC"
                reportPrefix = "TCReport"
            Case "SF"
                reportPrefix = "SFReport"
            Case Else
                reportPrefix = reportType
        End Select

        Return String.Format("{0}{1}", reportPrefix, yymm)
    End Function

    Public Shared Function GetReportName(ByVal reportType As String, ByVal year As Integer, ByVal reportCode As String) As String
        Return GetReportName(reportType, GetReportYYMM(year, reportCode))
    End Function

    Public Shared Function GetGrossReportName(ByVal year As Integer, ByVal reportCode As String) As String
        Return MyObj.GetReportName("SFChannelStat", year, reportCode)
    End Function

    Public Shared ReadOnly Property CurrMonthCode()
        Get
            Return MyObj.GetReportYYMM(Date.Now.Year, Date.Now.Month)
        End Get
    End Property

    '判斷是否為新分類
    Public Shared Function IsCatagory(ByVal value As String) As Boolean
        If value.IndexOf("_") <> -1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Const SelectAllValue As String = "All"
    Public Const SelectNoneValue As String = "None"
    Public Const PleaseChooseValue As String = "PleaseChoose"
    Public Const GroupMark As String = "#"

    '如果需要管理的頁面,就會紀錄Log
    Public Shared Sub SetLog(ByVal SqlText As String)
        SetLog(SqlText, Nothing)
    End Sub

    Public Shared Sub SetLog(ByVal SqlText As String, ByVal params As System.Data.SqlClient.SqlParameterCollection)
        If SqlText.IndexOf("ServerFarmOnlineCounter") <> -1 Then
            '去掉Global裡執行的語法
            Return
        End If
        Dim dv As DataView = MyObj.AuthPage.DefaultView
        dv.RowFilter = "Url like '%" & clsAuth.GetProgramUrl().Replace("%", "[%]").Replace("'", "''") & "%'"
        If dv.Count = 1 Then
            If Not params Is Nothing Then
                Dim param As SqlParameter
                For Each param In params
                    SqlText = SqlText.Replace("@" & param.ParameterName, "'" & param.Value & "'")
                Next
            End If
            MyObj.RecordLog(SqlText)
        End If
    End Sub

    Public Shared AuthPageDT As DataTable
    Public Shared Function AuthPage() As DataTable
        Try
            If MyObj.AuthPageDT Is Nothing Then
                Dim sql As New MyStringBuilder
                Dim dat As New clsData
                Dim dt As New DataTable

                sql.Length = 0
                sql.AppendFormat("select p.Url from ProtectedProgram p ")
                dt = dat.GetData(sql)

                MyObj.AuthPageDT = dt
            End If
        Catch ex As Exception
            MyObj.WriteLog(ex.ToString())
        End Try
        Return MyObj.AuthPageDT
    End Function

    '紀錄系統執行的Log
    Public Shared Sub RecordLog(ByVal strSql As String, ByVal params As Dictionary(Of String, Object))
        Dim temp_list As New List(Of SqlParameter)

        For Each key As String In params.Keys
            temp_list.Add(New SqlParameter(key, params(key)))
        Next
        If Not temp_list Is Nothing Then
            Dim param As SqlParameter
            For Each param In temp_list
                strSql = strSql.Replace("@" & param.ParameterName, "'" & param.Value & "'")
            Next
        End If
        RecordLog(strSql)
    End Sub

    Public Shared Sub RecordLog(ByVal Memo As String)
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("Insert into SystemLog(CreateBy,CreateDate,Url,FromIP,Memo) ")
        sql.AppendFormat("select ")
        sql.AppendFormat("'{0}' ", clsAuth.GetUserID())
        sql.AppendFormat(",Getdate() ")
        sql.AppendFormat(",'{0}' ", HttpContext.Current.Request.Url.Host & HttpContext.Current.Request.Url.PathAndQuery.ToString().Replace("'", "''"))
        sql.AppendFormat(",'{0}' ", MyObj.GetClientIP())
        sql.AppendFormat(",'{0}' ", Memo.Replace("'", "''"))
        sql.AppendFormat(";select '' ")
        dat.GetData(sql.ToString())
    End Sub

    Public Shared Function GetClientIP()
        Dim strIPAddr As String
        If Request.ServerVariables("HTTP_X_FORWARDED_FOR") = "" Or InStr(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), "unknown") > 0 Then
            strIPAddr = Request.ServerVariables("REMOTE_ADDR")
        ElseIf InStr(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), ",") > 0 Then
            strIPAddr = Mid(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), 1, InStr(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), ",") - 1)
        ElseIf InStr(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), ";") > 0 Then
            strIPAddr = Mid(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), 1, InStr(Request.ServerVariables("HTTP_X_FORWARDED_FOR"), ";") - 1)
        Else
            strIPAddr = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
        End If
        Return Mid(strIPAddr, 1, 30).Trim
    End Function

    Public Shared Function GetCoefficient(ByVal Signature As String) As String
        Dim sql As New StringBuilder
        Dim dat As New clsData
        Dim dt As DataTable

        sql.AppendFormat("Select isnull(Coefficient,1.00) as Coefficient from RetailPriceCoefficient ")
        sql.AppendFormat("where Signature='{0}' ", Signature)
        dt = dat.GetData(sql.ToString())

        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("Coefficient").ToString()
        Else
            Return "1.00"
        End If
    End Function

    Public Shared Function CheckItemName(ByVal ItemName As String) As Boolean
        Select Case ItemName
            Case "Discount and Bonuses", "S.Allow-off inv/FI", "Year-end-rebate-SD"
            Case "Year end rebate FI", "P.Allow-off inv/FI"
            Case "Clearances", "Returns", "Return-Mth end prov"
            Case "Early payment disc.", "Other Allow. NS-SD", "Other Allow. NS-FI", "Coupons"
            Case "Other promotions NS", "Net Sales DEG"
            Case "Environment contri.", "Std cost sales var"
            Case "DEG moving price", "Roy./Techn assist-FI"
            Case "Promtional allowance", "Misc. sales-loyalty program", "Std cost of sales"
            Case "Gross Sales", "Discounts & Bonuses", "Year-End-Rebates", "Promotional Allowanc" '加總部份
            Case "Total Returns", "REFERENCE NET SALES", "CONSOLIDATED CA NET", "TOTAL NET SALES"
            Case "STD Cost of Sales2", "STD COGs Variances", "PRODUCT CONTRIBUTION"
            Case Else
                Return False
        End Select
        Return True
    End Function

    Public Shared Function CheckYTDItemName(ByVal ItemName As String) As Boolean
        Select Case ItemName
            Case "Clearance & Returns", "Non Assigned Returns", "Coupons", "Early payment disc."
            Case "Environment subscription", "Gross Sales - Catalogue Sales"
            Case "Net Sales DEG", "Other Customer Allowances"
            Case "Promotion Discounts / Allowances", "Royalties / T.A.", "Std CSV", "Structural Discounts - Trade Allowances"
            Case "Total standard Cost Of Sales", "UNITS  Before Returns"
            Case Else
                Return False
        End Select
        Return True
    End Function

    Public Shared Function GenValidSheetName(ByVal sheetName As String) As String
        'allow 31 characters only
        If sheetName.Length > 31 Then
            sheetName = sheetName.Remove(31)
        End If

        '/\?*[]' is not allowed
        sheetName = sheetName.Replace("/", "")
        sheetName = sheetName.Replace("\", "")
        sheetName = sheetName.Replace("?", "")
        sheetName = sheetName.Replace("*", "")
        sheetName = sheetName.Replace("[", "")
        sheetName = sheetName.Replace("]", "")
        sheetName = sheetName.Replace("'", "")
        sheetName = sheetName.Replace(":", "")

        Return sheetName
    End Function

    Public Shared Function GetDivisionName(ByVal Division As String) As String
        Dim DivisionName As String = ""
        Select Case Division
            Case "CPD"
                DivisionName = "Consumer Products Division"
            Case "PPD"
                DivisionName = "Professional Products Division"
            Case "ACD"
                DivisionName = "Active Cosmetics Division"
            Case "LPD"
                DivisionName = "L 'Oréal Luxe Division"
        End Select
        Return DivisionName
    End Function

    Public Shared Function GetMonthName(ByVal Month As Integer) As String
        Dim MyDate As New Date(2013, Month, 1)
        Return MyDate.ToString("MMMM", CultureInfo.CreateSpecificCulture("en-US"))
    End Function

    Public Shared Function GetAreaCode() As String
        Return "TW"
    End Function

    Public Shared Function GetNum(ByVal itemValue As String) As Decimal
        If IsNumeric(itemValue) Then
            Return Convert.ToDouble(itemValue)
        Else
            Return 0
        End If
    End Function

    Public Shared Sub SetMPStatus(ByVal ddlControl As DropDownList, ByVal ViewPoint As String)
        Dim NonPLVList As New List(Of String)
        NonPLVList.Add("AllFG")
        NonPLVList.Add("N1")
        NonPLVList.Add("B1")
        NonPLVList.Add("R1")

        If Left(ViewPoint, 3) = "PLV" Then
            For i As Integer = 0 To NonPLVList.Count - 1
                ddlControl.Items.Remove(ddlControl.Items.FindByValue(NonPLVList.Item(i)))
            Next
        Else
            For i As Integer = ddlControl.Items.Count - 1 To 0 Step -1
                If Not NonPLVList.Contains(ddlControl.Items(i).Value) Then
                    ddlControl.Items.Remove(ddlControl.Items.FindByValue(ddlControl.Items(i).Value))
                End If
            Next
        End If
    End Sub

    '去掉所有小數點後面的0
    Public Shared Function RemoveDTZero(ByVal dt As DataTable) As DataTable
        For i As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
                Dim myString As String = dt.Rows(i)(j).ToString()
                If (myString.IndexOf(".") <> -1) Then
                    myString = myString.TrimEnd("0")
                    If myString.EndsWith(".") Then
                        myString = myString.Substring(0, myString.Length - 1)
                    End If
                    dt.Rows(i)(j) = myString
                End If
            Next
        Next
        Return dt
    End Function
End Class
